﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using ColorApplication.Constants;
using Microsoft.SqlServer.Server;

namespace ColorApplication.DataAccess
{
    public class ColorSayDao
    {
        private static ColorSayDao _instance;
        private string _connectionStringCache = string.Empty;

        private ColorSayDao() { }

        public static ColorSayDao Instance
        {
            get { return _instance ?? (_instance = new ColorSayDao()); }
        }

        public string ConnectionString
        {
            get
            {
                if (string.IsNullOrEmpty(_connectionStringCache))
                {
                    InitializeConnectionString();
                }
                return _connectionStringCache;
            }
        }

        public void InitializeConnectionString()
        {
            _connectionStringCache = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
        }

        protected SqlConnection CreateSqlConnection()
        {
            return new SqlConnection(ConnectionString);
        }

        protected SqlCommand CreateSqlSpCommand(string spName, SqlConnection sqlConnection)
        {
            var command = new SqlCommand(spName, sqlConnection);
            command.CommandType = CommandType.StoredProcedure;
            return command;
        }

        internal DataSet GetAttributesFamiliesSelectByTopCount(int topCount)
        {
            var ds = new DataSet();

            var dbConnection = CreateSqlConnection();
            var command = CreateSqlSpCommand(StoreProcedureName.GetAttributesFamiliesSelectByTopCount, dbConnection);

            //<Parameter>
            var paramRowCountNo = new SqlParameter("@TopCount", SqlDbType.Int) { Direction = ParameterDirection.Input, Value = topCount };
            command.Parameters.AddRange(new[] { paramRowCountNo });

            var da = new SqlDataAdapter(command);

            dbConnection.Open();
            try
            {
                da.Fill(ds);
            }
            finally
            {
                dbConnection.Close();
            }

            return ds;
        }

        public void UpdateSelectAttrsForOption(List<SqlDataRecord> tvpAttrIds)
        {
            var dbConnection = CreateSqlConnection();
            var command = CreateSqlSpCommand(StoreProcedureName.UpdateSelectAttrsForOption, dbConnection);

            //<Parameter>
            var attsIdsParam = new SqlParameter("@ListAttrIDs", SqlDbType.Structured)
                                   {
                                       Value = tvpAttrIds,
                                       TypeName = "dbo.utblListIds"
                                   };

            command.Parameters.AddRange(new[] { attsIdsParam });

            dbConnection.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            finally
            {
                dbConnection.Close();
            }
        }

        internal DataSet GetAnalysisUrlForReport(DateTime? fromdate, DateTime? todate)
        {
            var ds = new DataSet();

            var dbConnection = CreateSqlConnection();
            var command = CreateSqlSpCommand(StoreProcedureName.GetUrlsForAdminReports, dbConnection);

            //<Parameter>
            var paramStartDate = new SqlParameter("@StartDate", SqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = fromdate };
            var paramToDate = new SqlParameter("@EndDate", SqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = todate };
            command.Parameters.AddRange(new[] { paramStartDate, paramToDate });

            var da = new SqlDataAdapter(command);

            dbConnection.Open();
            try
            {
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbConnection.Close();
            }

            return ds;
        }

        internal DataSet GetAttributesFamilyForReport()
        {
            var ds = new DataSet();

            var dbConnection = CreateSqlConnection();
            var command = CreateSqlSpCommand(StoreProcedureName.GetAttributesfamiliesSelect, dbConnection);

            var da = new SqlDataAdapter(command);

            dbConnection.Open();
            try
            {
                da.Fill(ds);
            }
            finally
            {
                dbConnection.Close();
            }

            return ds;
        }
    }
}